home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Libris Britannia 4
/
science library(b).zip
/
science library(b)
/
CUGUK
/
APPLICAT
/
C034.ZIP
/
DBQ.NRO
< prev
next >
Wrap
Text File
|
2010-11-01
|
50KB
|
1,432 lines
.de TH
.in 4
.rm 76
.he |$0|$2|$1|
.fo ||-#-||
.in 8
.rm 72
.bp
.en
.de CH
.in 4
.rm 76
.he |$0|$2|$1|
.fo ||-#-||
.in 8
.rm 72
.bp
.SH "Syntax"
.en
.de PP
.sp 1
.ti +4
.en
.de SH
.sp 1
.ti -4
.bo
$0
.sp
.en
.de HB
.sp 2
.SH "Details"
.sp
.in 10
.en
.de HY
.br
.ti -2
-
.en
.de EH
.sp
.in 8
.en
.TH "List of contents" CONTENTS "DBQ database query language"
.sp
INTRODUCTION
.sp
.in +8
Introduction
.br
Overview of facilities
.sp
.in -8
COMMANDS
.sp
.in +8
Commands
.br
Interaction
.br
Procedures
.br
Command files
.br
Comments
.br
Editing
.br
File names
.br
Selection expressions
.br
Relational operators
.br
Aliases
.br
Notation
.sp
.in -8
COMMAND REFERENCE
.sp
.in +8
create
.br
insert
.br
update
.br
print
.br
find
.br
import
.br
export
.br
extract
.br
compress
.br
sort
.br
erase
.br
rename
.br
define
.br
show
.br
enter
.br
set
.br
help
.br
exit
.br
command file inclusion (@)
.sp
.in -8
DATABASE MAINTENANCE
.sp
.in +8
Introduction
.br
Setting up
.br
Security
.br
Reformatting
.sp
.in -8
USING THE DATABASE
.sp
.in +8
General points
.br
Query style
.br
Making a log file
.br
Joining multiple databases
.br
Limits
.sp
.in -8
REPORT FORMATTING
.sp
.in +8
Report formatting
.br
Field specifiers
.br
Heading specification
.br
Pause specification
.br
Uses of format files
.sp
.in -8
MESSAGES AND ERRORS
.sp
.in +8
Introduction
.br
Informational messages
.br
Error messages
.br
Other error situations
.sp
.TH "Introduction" "INTRODUCTION" "DBQ Database query language"
.SH "Introduction"
DBQ is a powerful structured query language modelled on query language
facilities available on large minicomputers. It is designed for use by
those wanting an English like command language to enter
database manipulation commands, rather than a menu driven interface.
Because of this it cannot be used immediately by a
complete novice in the same manner as menu driven systems, but it is easy
to learn the basic commands in a few hours, and once
learnt, it is considerably faster and more flexible to use.
.sp
The commands operate against disk resident files. Because of this, there
is no need to load or save database files explicitly, and the system is
more resilient to power failures. However, due to the disk accessing, the
processing is generally slower than memory resident databases.
.sp
The query language provides a comprehensive set of functions for the
creation, maintenance and enquiry of the database format supported by
the DBQ system.
.sp
The query language is the prime means of interaction with the database
and it is important to become properly conversant with the many powerful
commands before undertaking any major work using it.
.sp
Because there is a finite limit on memory, many features found in more
expensive databases have been left out of DBQ. Whilst important, these
are by no means essential for personal databases. Such features include
full arithmetic capabilities for updating fields and reporting, full
report formatting with control totals, validation of input data at entry
and high speed keyed access to data. Because of these, and other limitations,
the database should be considered unsuitable for large scale commercial usage.
.SH "Overview of facilities"
Database level commands permit the creation of a database, erasing a database,
renaming a database, sorting a database on one or more fields, importing and
exporting database records from and to external applications, and compressing
a database to remove any deleted records from it.
.sp
Record level commands include the insertion of records interactively or from
a data file, the updating of selected records, the deletion of selected
records, the selection of records into a working database, and the listing
of selected records in tabular form or in a form specified by a format file.
.sp
Specific fields can be selected in the update, find and print operations,
and it is possible to do a relational join operation, whereby selected fields
from two or more databases can be selected from records matched on some field
or fields in the databases.
.sp
Sequences of commands and data can be input from a file, and short sequences
of commands can be defined as procedures, which can be executed simply by
use of a keyword. In fact, simple groups of words can be defined in this way
allowing aliases for keywords and keyword combinations. The command files
and procedures can input variable data to be used in the evaluation of the
command file or procedure. Command files and procedures can be nested to a
certain level.
.sp
Various options can be set, allowing control of the case sensitivity,
causing a command file to be listed as it is executed, and switching
a log file in or out to permit keyboard input to be logged for a number
of purposes. Also tabulations can be split into pages, and the page length
can be varied to accommodate different lengths of paper or different print
formatting.
.sp
Full online help is provided, permitting selection by initial letter of
help topics.
.sp
The command line input uses the CPM+ line input facilities, permitting full
editing to be carried out on the current line, including the copying of the
previous command using ^W. You are referred to the CPM+ documentation or
the CPM+ help file for more information on this.
.sp
The database is initially built to hold 10 records, but grows as new records
are added. Records are updated in situ, and are deleted by flagging them as
deleted. Deleted records can be compressed out of the database, and for
occasions where a deletion selection was a bit too greedy it is possible
to dump the deleted records out to a file for subsequent editing and
re-input.
.TH "Command reference" COMMANDS "DBQ Command Reference"
.SH COMMANDS
This section details each command in turn, providing the format of the
command and details of the commands action. An example is also provided.
.SH Interaction
Commands may be entered in free form. This means that line termination
has no special meaning, and any number of spaces or tabs can be inserted
at the start or end of a line or between elements. A command may be
broken over more than one line, whereupon continuation prompts are issued
to show that further input is expected. Where the end of a command has
to be indicated, for example at the end of a print selection, either a
new command or the command terminator ";" may be used. More than one
command can be issued on the same line, although it is usually prudent
to let each command execute before issuing the next in case of failure,
and because an error will cause unused input for a command to be rejected
as a syntax error.
.SH Procedures
Procedures provide a means of entering a predefined block of text as
input, identified by a name. They are included in the input by simply
using their name, and can be used at any point of input where a keyword
or identifier is expected. They can represent anything from a single
word up to a set of commands, and can contain statements within
them to prompt for variable data to be used when expanding them.
.SH "Command files"
Command files are like procedures, but instead of fetching text from
memory, the text is input from a file. The text can represent anything
a procedure can represent, and can also contain prompting statements,
for use as variable input, or as pauses in the command file execution.
.SH Comments
Comments can be put on any line after a number sign "#",
this usually being used for command files, or when logging the input. The
comment acts as a command terminator, so cannot be inserted halfway through
a command.
.SH Editing
Line input is done via the CP/M line input routine, permitting editing to
be done on the current input line, and the previous line to be recalled.
This is useful when entering a long query line, as errors can be easily
corrected. Data input for the INSERT and UPDATE functions is also done
via this routine.
.SH "File names"
File names with extensions indicated can be entered simply as the
name without the extension, or if another extension is used, they may
be entered within quotes, whereupon this is used as the full name.
Database names cannot under most circumstances be entered as a string,
and should always be entered as an identifier. A drive prefix can be
put on any filename in either form, but a user area specifier is not
permitted when entered in identifier form.
.SH "Selection expressions"
A selection expression is a relational expression involving one or more
database fields. A relational expression is simply a comparison between
a field and a value (or another field). The types of comparison are called
relational operators, and are listed below. A compound relational expression
involving brackets and the logical operators 'and' and 'or' can be used
to specify a complex selection.
There follows some examples of selection expressions:-
.sp
balance > 10000
.sp
name } "Smith"
.sp
sex = "male" and (age > 65 or age < 20)
.sp
Note that in the last example, the brackets are necessary to join the
two age comparisons together for use in the 'and' operation, otherwise
the 'age < 20' would be separate and would satisfy the expression by
itself.
.SH "Relational operators"
The relational operators are as follows.
.sp
= Equals
.sp
> Greater than
.sp
< Less than
.sp
>= Greater than or equal to
.sp
<= Less than or equal to
.sp
<> Not equal to
.sp
} Contains (substring search)
.sp
For numeric fields, a numeric comparison is made. For character
fields, a lexicographic comparison is made using the ASCII
collating sequence. For the contains operator, a string search
is done regardless of field type. The contains operator can be
used as a "contained in" operator by simply reversing the
operands (e.g. with "montuewed" } day).
.bp
.SH "Aliases"
When specifying complex queries involving qualification of field
names, and when joining a database with itself, a more convenient
way of specifying fieldnames and database names is provided.
.sp
The alias notation permits a temporary name, or 'alias' to be
specified for field names or database names. This is done by
specifying the alias directly after the normal name, without the
comma separator used to specify elements in a list. The alias name
can be used wherever the original name can be used, apart from in the
field list or database list used to specify the aliases. Typically it
would be used in a selection expression, or in a format file. The field
alias is also used in the title line in a tabulation, and as the field
name in a current working database created by a FIND command.
.sp
For example, to join a family database between name and parent name,
the following selection could be entered.
.sp
.nf
DBQ> print family.name, parents.age dads_age
DBQ> of family, family parents
DBQ> with family.parent = parents.name ;
.fi
.sp
This shows the database "family" being given an alias "parents" to show
which instance of the database the age in the print list and the name in
the comparison should come from. The parents age field also has an alias
specified "dads_age" to allow the report produced to have a more applicable
heading.
.SH Notation
In this section, keywords are shown in uppercase, and other identifiers
are shown in lowercase. This is done to permit the keywords to be easily
identified, and does not imply that they must be used in uppercase. In fact
the examples show normal command input in lowercase, and this is the
recommended mode of use.
.sp
Square brackets indicate optional elements of the command language.
A second occurence of an element or set of elements within square
brackets generally indicates that the set can be repeated a number of times.
.TH "Command Summary" SUMMARY "DBQ Command Reference"
.in 0
.nf
CREATE database fieldname type size [scale] [fieldname type size [scale]]
ERASE database
RENAME database newname
INSERT database
DELETE selection ;
UPDATE fieldnames OF selection ;
PRINT [USING formatfile] fieldnames OF selection [INTO reportfile] ;
FIND fieldnames OF selection ;
SORT database BY fieldname [direction] [fieldname [direction]] ;
IMPORT datafile INTO database
EXPORT [DELETED] database [INTO datafile] ;
EXTRACT database [INTO definitionfile] ;
COMPRESS database
DEFINE procedure / ENTER procedure
SHOW {procedure} ;
SET [NO] [FOLD] [VERIFY] [LOG] [PAGE [pagelength]] ;
HELP
EXIT
Where identifiers are:-
database, newname : database file name (Identifier) (Ext. .DBQ)
fieldnames : qualfieldname[+] [, qualfieldname[+] ...] | all[+]
qualfieldname : [database.]fieldname
fieldname : field name (identifier)
type : NUM | CHAR
size : 1 to 12 NUM, 1 to 128 CHAR
scale : 0 to 10 for NUM fields only
direction : ASC (default) | DESC
selection : database [, database ... ] WITH expression
expression : [NOT] comparison [AND | OR [NOT] comparison ...]
comparison : primary relation primary
primary : qualfieldname | string | number
relation : = | > | < | >= | <= | <> | }
procedure : procedure name (Identifier)
pagelength : length of paper in lines less 6 for heading
datafile : data file name (Identifier) (Ext. .DAT)
formatfile : format file name (Identifier) (Ext. .FMT)
reportfile : report file name (Identifier) (Ext. .REP)
definitionfile : definition file (Identifier) (Ext. .DEF)
identifier : Alpha [ alpha | "_" ...]
.fi
.in 8
.bp
.CH "Create database" CREATE "DBQ Command Reference"
CREATE database fieldname type size [scale] {fieldname ...};
.HB
.HY
database is the database name
.HY
fieldname is a field name
.HY
type is the field type (CHAR | NUM)
.HY
size is the field length
.HY
scale is the scale (decimal places) to be used when printing totals.
The default value is 0 indicating an integer field (no decimal places).
This scale is not used for field storage, although it is indicated
whenever a field is input or updated. Stored decimal places beyond the
scale are ignored in totalling.
.EH
The field name, type, length (and scale if required) are specified for
each field in the record.
.SH Example
create accounts name char 10 acc_num num 6 balance num 8 2 ;
.sp
This creates a database called "accounts", with three fields - name,
acc_num and balance.
.fi
.CH "Insert records" INSERT "DBQ Command Reference"
INSERT database
.SH Details
This command allows records to be inserted in the database.
It prompts for the fields in the record one by one. Brackets show the width
of the field.
[string] and <numeric> fields are indicated by the type of bracket.
A number after <numeric> brackets indicates the number of decimal places.
The brackets are only displayed as a visual guide, and the length shown
is not enforced on input, although any field input longer than the width
shown will be truncated when stored.
.sp
Pressing just ENTER on the first field terminates input, and displays the
number of records inserted.
.SH Example
.nf
DBQ> insert accounts
name [john smith]
acc_num <123 >
balance <2.34 > 2
------
name [ ]
[ 1 records inserted ]
.fi
.CH "Update records" UPDATE "DBQ Command Reference"
UPDATE {fields | ALL } OF selection;
.HB
.HY
fields is a field list
.HY
selection is a record selection expression
.EH
This allows you to update the specified fields in the selected
records. The original value is printed first, and a prompt is issued
to allow a new value to be input. If no value is entered before
pressing ENTER, the original value is retained. After all records
selected have been updated, a count of records updated is displayed.
.SH Example
update balance of accounts with name = "john smith";
.nf
balance = 2.34
balance < > 2
.fi
.CH "Print records" PRINT "DBQ Command Reference"
.nf
PRINT [USING filename] [{ fieldname[+],
fieldname[+] . . . | ALL[+] } OF] selection [INTO repname] ;
.fi
.HB
.HY
USING filename indicates a format file (ext=.FMT)
to be used for output formatting.
.HY
field[+] is an optionally qualified field name with optional
totalling (+) specified for numeric fields.
.HY
ALL indicates all fields with optional totalling (+) of all
numeric fields.
.HY
selection is a record selection expression.
.HY
repname is the name of an output file (ext=.REP) to be used instead of the
terminal for output. (LST will cause output to the printer).
.EH
For format file details, see the appropriate section.
.sp
If the format file is not used, output is tabulated under headings in a
fairly simple and straightforward manner. If totalling is specified,
totals are printed for the selected records at the end of the tabulation.
Headings consist of the field names forced to uppercase, with underscores
changed to spaces. Aliases can be used to give the fields alternative
headings.
.sp
Totalling is usually only specified for numeric fields, but character
fields with leading numerics can also be totalled if required.
.SH Example
.nf
print name, acc_num account, balance+ of accounts
with name } "smith";
.fi
.sp 2
.in -8
.nf
NAME ACCOUNT BALANCE
==============================
a smith 23 12.34
j smith 124 100.30
kb smith 21 4.20
==============================
116.84
[ 3 records found ]
.fi
.in +8
.CH "Select records" FIND "DBQ Command Reference"
FIND { field, field . . . | ALL } OF selection ;
.HB
.HY
field is an optionally qualified field name
.HY
ALL indicates all fields and can be abbreviated to *
.HY
selection is a record selection expression
.EH
Creates a database called "CURRENT" containing the selected records.
.sp
This command is used to create a working collection of records
which can have sorts, selective deletions or prints made against it.
It can also be used to cut down the processing necessary when
joining a number of databases.
.sp
It can also be used to reformat a database, although other means
are available to achieve this if fields are to be added (see the
section on reformatting).
.sp
The names of fields can be changed by the use of aliases.
.SH Example
find name, acc_num account of accounts with name } "smith";
.br
rename current smiths
.br
print smiths;
.sp
This selects the name field and the acc_num field (renamed to
account) of those records containing the string "smith" in the
name. It then renames the current database as the database "smiths",
and prints it.
.CH "Import records" IMPORT "DBQ Command Reference"
IMPORT filename INTO database
.HB
.HY
filename is the name of a data file (ext .DAT) containing
the values of the fields, one per line.
.HY
database is the name of the database to append the records to.
.EH
.SH Example
DBQ> import accdata into accounts
.SH NOTE
Data to be transferred from a BASIC program should simply be
output one field per line using successive PRINT statements.
.sp
e.g. Subroutine for outputting data, assuming stream 9 is opened
for output, and closed afterwards.
.sp
.nf
2000 REM OUTPUT DATA FOR DATABASE
2010 FOR IND% = 1 TO RECCOUNT%
2020 PRINT #9,ACCNAME$(IND%)
2030 PRINT #9,ACCNUM%(IND%)
2040 PRINT #9,ACCBAL(IND%)
2050 NEXT IND%
2060 RETURN
.fi
.CH "Export records" EXPORT "DBQ Command Reference"
EXPORT [DELETED] database [INTO filename]
.HB
.HY
database is the name of the database to export records from
.HY
filename is the name of a file (ext .DAT) to receive the records.
If omitted, the records are written to the terminal.
.HY
the "DELETED" option exports those records that have been marked
as deleted since the file was created or compressed. It is used to
recover overambitious deletions.
.HY
records are written to the output file one field per line.
.EH
.SH Example
DBQ> export deleted accounts into deldata
.CH "Extract definition" EXTRACT "DBQ Command Reference"
EXTRACT database [INTO filename]
.HB
.HY
database is the name of a database
.HY
filename is a file (ext .DEF) to receive the definition.
If omitted, the definition is written to the terminal - this
is handy for reminding you what fields are defined.
.HY
The definition is specified as the commands necessary to
create the database again at the current size.
.EH
.SH Example
DBQ> extract accounts
.sp 2
create accounts
.br
name char 10
.br
acc_num num 6
.br
balance num 8 2
.br
;
.CH "Compress database" COMPRESS "DBQ Command Reference"
COMPRESS database
.SH Details
- database is a database name.
.sp
This compresses active records at the start of the database file,
and frees up space at the end for further records to be added.
.SH Example
DBQ> compress accounts
.br
[20 records freed]
.SH NOTE
To reduce the size of the file as well, assuming there is sufficient
disk space, the following sequence may be used instead
.sp 2
DBQ> find all of <database>;
.br
DBQ> print; # To check if everything is O.K. before erasing old file
.br
DBQ> erase <database>
.br
DBQ> rename current <database>
.CH "Sort database" SORT "DBQ Command Reference"
SORT database BY keyname1 {, keyname2} . . . ;
.HB
.HY
database is a database name
.HY
keyname is the name of a field to sort on, optionally followed
by "ASC" (default) or "DESC".
.HY
The sort order is major through minor (e.g. keynameN
within keynameN-1 . . . within keyname2 within keyname1).
.HY
Records are sorted in situ. It takes a while for a large database.
.HY
It is not advisable to abort a sort halfway through - be patient.
.EH
.SH Example
sort accounts by name, balance desc;
.CH "Erase a database" ERASE "DBQ Command Reference"
ERASE database
.SH Details
- database is the name of a database
.sp
The named database file is erased from the disk. It cannot be
recovered except by immediate use of a program such as UNERA.
.sp
This command is equivalent to the CPM ERA command, which
can be used as an alternative.
.SH Example
DBQ> erase current
.CH "Rename database" RENAME "DBQ Command Reference"
RENAME olddatabase newdatabase
.HB
.HY
olddatabase is the existing name of a database
.HY
newdatabase is the desired new name for the database
.EH
The use of this command results in the same action as use of
the CPM REN command, which can be used as an alternative.
.SH Example
DBQ> find all of employees with dept = 300;
.br
DBQ> rename current ourdept
.CH "Define procedure" DEFINE "DBQ Command Reference"
DEFINE procname
.HB
.HY
procname is the name of the procedure to be defined
.EH
DBQ prompts you for the definition on subsequent lines. An empty line
is used to terminate the definition. As procedure definitions overwrite
previous definitions, a null definition effectively deletes a procedure.
.sp 2
Procedures can be used for a wide range of purposes.
.sp
In its most simple form it can be used to set up synonyms
for the query language command keywords for a non-English
language, or for personal preference.
Also it can be used for the following :-
.sp
To replace sequences of words with a single keyword.
.sp
To specify part of, or all, a complex query.
.sp
To specify a sequence of commands.
.sp
To specify a sequence of commands with variable parameters input
via the ENTER command (see ENTER command for details).
.sp
Procedures can be nested. That is, a procedure can refer to another
procedure within its definition. Procedures referring to procedures
referring back to the original will cause problems when used, and
must be avoided.
.SH Example
DBQ> define total
.br
DEFINE> print all+ of
.br
DEFINE>
.br
DBQ> total accounts;
.CH "Show procedure" SHOW "DBQ Command Reference"
SHOW procname
.sp
SHOW ;
.HB
.HY
procname is the name of the procedure to be listed
.HY
if procname is replaced by a terminator or keyword,
a list of currently defined procedures is shown.
.EH
.SH Example
DBQ> show list
.sp
print all of
.br
DBQ> show ;
.sp
list
.br
DBQ>
.CH "Prompt for value" ENTER "DBQ Command Reference"
ENTER procname
.sp
ENTER "procname"
.SH Details
- procname is an identifier
.sp
Causes a prompt to be issued for the identifier, and after reading one
line, sets the procedure to that value. A null entry deletes the procedure.
This is used in command files, and in other procedures, to prompt for input
as part of a sequence of commands. The procedure in this case is just a
simple value, but it is called in the same manner as a full multiline procedure.
.sp
Where the procname is specified inside quotation marks, it indicates that the
definition should be contained within quotation marks. This is used when
entering string literals for comparison purposes.
.SH Example
.nf
DBQ> define findemp
DEFINE> enter empno:
DEFINE> enter "name:"
DEFINE> print all of employees with empno = empno:
DEFINE> and name = name: ;
DEFINE>
DBQ> findemp
DBQ> Enter empno:
.fi
.CH "Set options" SET "DBQ Command Reference"
SET [NO] [FOLD] [VERIFY] [LOG] [PAGE];
.SH Details
This permits options to be set, or unset if "NO" is first specified.
.sp
The options are:-
.SH NO
The word "NO" changes the sense of the setting to that of unset.
Thus "SET NO FOLD;" would unset the fold option.
.SH FOLD
Fold permits alphabetic case folding on comparisons.
.sp
e.g. "FrEd" would match with "fReD"
.SH VERIFY
Verify causes the contents of command files to be output during execution.
This may be used to check that the command file is running correctly. Comments
should be inserted in the command file to keep track of where you are.
.SH LOG
Log causes a log file to be created, and all input from the terminal to
be written to it. Unsetting log simply switches off the writing, and
subsequent setting of the log simply switches on the writing again.
.sp
When the "exit" command is issued, the file is closed and a message output
saying that it has been closed, prior to DBQ finishing.
.SH PAGE
Page allows pagination to be done on tabulations. Simply setting page will
set a pagelength of 60 detail lines, correct for 11 inch depth paper. Other
page depths can be accomodated by quoting the number of detail lines to be
printed per page after the set page command.
.sp
e.g. set page 20
.sp
sets the pagelength at 20 detail lines.
.sp
If page in unset, an effective page length of 32000 is set up, and no page
number will be printed.
.sp
Pagination is effective on both normal print tabulations and on format file
driven output, for which a special value may again be required depending on
page layout.
.SH "Set display"
After the options have been set or unset, the current settings are displayed.
Each option name is followed by a number. For FOLD, VERIFY and LOG, a value
of 0 indicates unset, and 1 indicates set. For PAGE, the page length is
displayed.
.SH Example
set fold page 62 no verify ;
.sp
Fold = 1, verify = 0, log = 0, page = 62
.CH "Online help" HELP "DBQ Command Reference"
HELP
.sp
?
.SH Details
This causes an initial page of help information to be output to the
terminal. A prompt is then issued to press ^Z, ENTER, or a character.
.sp
If ENTER is pressed, the next page of help is printed.
.sp
If ^Z is pressed, the program returns to the DBQ> prompt.
.sp
If a character is pressed, the next help page relating to a topic
starting with that character is displayed. If a character is pressed
which is not the initial of any remaining help frame, DBQ returns to
the DBQ> prompt.
.SH Example
DBQ> help
.sp
--- page of help ---
.sp
Press ^Z to abort, ENTER to continue, or x for help on x...
.br
c
.sp
--- page of help on "create" ---
.CH "Exit from DBQ" EXIT "DBQ Command Reference"
EXIT
.SH Details
This returns you to the CPM+ prompt.
.sp
If a log file is currently open (although not necessarily being written
to), it is closed and a message to that effect is displayed.
.SH Example
DBQ> exit
.br
A>
.CH "Command files" @ "DBQ Command Reference"
@filename
.SH Details
- filename is the full name of a file containing commands.
.sp
This causes the contents of the specified file to be inserted at the
current point in the input. The command file may contain any input,
apart from the response to an "enter" request.
.sp
If "verify" is set, the contents of the file are output to the terminal
as it is read.
.sp
If the file "DBQINIT.CMD" is present when DBQ is started, it is executed
initially, and can be used to set up procedure definitions etc.
.sp
Comments can be inserted in a command file, or indeed in direct input,
by preceding them with "#". Everything on a line after a "#" is ignored.
.sp
The ENTER command can be used to input variable data for use within the
command file, in a similar manner as within a procedure. Also, an ENTER
command for a dummy variable can be used to halt the command file execution
until you respond to the enter request. The dummy variable can be immediately
used as a command on the next line, and will allow you to enter "exit" to
exit from DBQ if there is a problem.
.TH "Database administration" MAINTENANCE "DBQ Usage notes"
.SH Introduction
To effectively control your databases, it is necessary to think beyond
just setting them up and using them.
.sp
This section describes some requirements of proper database administration
and suggests ways of achieving these.
.SH "Setting up"
A number of things can be done to make life easier when using DBQ.
The DBQINIT.CMD file can be used to set up procedures to be used either
generally or against a specific database. These procedures may be simple
abbreviations for database print requests etc. or they can be larger
sequences of commands to enable interactive working against the database
using the "enter" command.
.sp
It is also possible, on the Amstrad CPC, to set up common commands on
the keypad. A sample keypad definition file is provided which can be
used as the basis of a custom keypad definition. The CCP key configuration
is useful also, as it sets the cursor keys to support the CCP editing
functions used by DBQ.
.SH Security
Security covers two aspects of data management. The first is the protection
of confidential information. For personal computers this is not usually a
problem, but if you do share a machine with others, it is suggested that
you keep confidential databases on specific disks which you can put in a
safe place.
.sp
The other aspect of security is insurance against data loss. This can be
achieved for most purposes by making regular backup copies of your database
disks. If you are planning on doing a long session on a database, firstly
copy the database to a backup disk, then set the log option when you start
the DBQ session. If something goes wrong during the session, the log file
will contain the commands you entered and can be used as a command file
against a new copy of the database from the backup (do not use the backup
itself!). It is possible to edit the command file to truncate the file
at some known point. It is then possible to recover nearly up to the point
at which things went wrong.
.SH Reformatting
It is possible to reformat the database in a number of ways.
.sp
When a FIND
command is done, a CURRENT database is created which can be renamed as
a permanent database. Thus fields can be renamed or dropped, or data from
more than one database can be used to form a new one. If a new field is
needed in a database, it can be added by creating a temporary database with
just one record containing the new field or fields in it, with the initial
value, and joining this with the main database in a select operation.
.sp
It is also possible to reformat a database by exporting the records, creating
a new database with the required format, editing the data file and importing
the datafile into the new database. It may be useful to specify a format file
to create an output file already in the new format to be imported - e.g.
.sp
>field2<
.br
>field1<
.br
newfield value
.br
>field3<
.sp
If this is used to PRINT the old database containing three fields, the
data can then be imported into the new database containing four fields.
.TH "General notes" "GENERAL" "DBQ usage notes"
.SH "General points"
DBQ is designed to be simple to use, and reasonably foolproof. There
is no need to explicitly open or close databases, or to do saves to the
disk, as the database is opened and closed for each operation. This means
that when you are at the DBQ prompt, there are no databases currently
open, and it is safe to abort the program using ^C, or by taking out the
disk and switching off the computer.
.SH "Query style"
Whilst the query facility is very powerful, it is often not the best
approach to request a large complex selection at one go. If you are using the
query facility as a means of choosing from the available records, it is
better to firstly see how many records a more general search will produce,
and then add further refinements until a suitable subset is found. For
example, if we have a database of cars containing information about all
current production cars, and we wish to select one to suit a particular
customer, we can reduce the selection on some important criteria firstly,
such as the price, and then select from the resultant cars. For example:-
.sp
DBQ> find cars with price < 5000 and price > 2500;
.br
DBQ> print current with doors = 4 and mpg > 40;
.SH "Making a log file"
There are two ways of making a logfile of the interaction with DBQ. The
SET LOG option allows input to be logged, and the resultant file can be
used as a command file, either directly or after being edited.
.sp
It is also possible to use the CPM+ 'put' transient to redirect the
console output to a file, for example by using the
command 'put console to file conout.log'. This will permit a full log of
both input and output to be obtained.
.SH "Joining multiple databases"
One of the properties relational databases is the ability to join
together the data from two or more databases, based on the relationship
between them. DBQ uses the power of the selection expression to not only
express the required relationship between the databases, but also to
select from the resultant data that which meets selection criteria in
the same manner as selection from a single database. The relationship
can be expressed not only in terms of single field equivalence, but of
multi-field relationships.
.sp
For a simple multi-database join, a straightforward comparison on the
related fields is all that is required.
.sp
DBQ> print all of orders, parts with order.partno = parts.partno;
.sp
For a more complex join, the required relationship will need further
specification.
.sp
DBQ> print all of employees, depts with employees.dept >=
.br
DBQ> depts.lowdept and employees.dept <= depts.highdept;
.sp
This joins the employees file with the department records for which they
are within the department number range.
.sp 2
For selections involving more than two databases, the extra relations
can be specified between any of the participating databases. It is also
possible to join a database with itself, although this demands the use of
a database name alias, and appropriate qualification. Further details of this
are provided in the section on aliases.
.SH Limitations
The various limits that DBQ has on data storage are as follows:-
.sp
Maximum size of any one database file - 64k bytes.
.sp
Maximum numeric field length (including sign and point) - 12 bytes.
.sp
Maximum number of decimal places - 10.
.sp
Maximum character field length - 128 characters.
.sp
Maximum number of fields in database - 30.
.sp
Maximum field name length - 10 characters.
.sp
Maximum database name length - 10 characters.
.sp
Maximum procedure name length - 10 characters.
.sp
Maximum command line length - 128 bytes.
.br
- Although a number of lines can make up a command.
.sp 2
As well as these limits, there is a limit on the amount of dynamic
memory that can be allocated for file buffers, etc. This is quite
high, and handles a fairly complex query against three database
files of 5 fields at the same time as using a command file for input,
a format file for output formatting, output to a report file, with a
current log file being produced, and with about 2k to 3k of procedure
definitions. Details of how to deal with a memory full error are
contained in the section on error messages.
.TH "Output formatting" "REPORTS" "DBQ usage notes"
.SH "Report formatting"
The PRINT command normally lists the records in tabular form. Other
formats are possible using the format file. The USING option on the PRINT
command can specify the name of a format file (Default extension .FMT).
This format file contains the text to be output, together with an indication
of where the fields are to be printed. In its simplest form, it contains the
field specifiers only, but it can also contain heading text and a flag
character to cause a pause at the terminal after each record.
.sp
.SH "Field specifiers"
Field specifiers come in two forms, as follows.
.sp
<fieldname>
.sp
and
.sp
>fieldname<
.sp
The first of these indicates that the field should be printed as its full
width. For character fields, the field is left justified with space fill
to the right. For numeric fields, the field is right justified with space
fill to the left.
.sp
The second form indicates that the field should be printed in minimum
width form, whereby only the necessary characters are printed, and no
space fill occurs. This form is used for such purposes as mailmerge
where the surrounding text can be properly concatenated with the field
value.
.sp
Note that the field name used can be the normal field name, or an alias
if one has been specified. It is preferable to use aliases if qualification
is otherwise necessary due to the field name not being unique to one
database.
.SH "Heading specification"
A percent sign "%" at the start of the file indicates that any following
text up to the next percent sign is heading text and should only be output
once at the start. Careful positioning of the percent signs permits the
correct spacing of the heading lines. The number of heading lines should
be used when calculating any page length setting.
.sp
When pagination is in effect (see the SET PAGE option), the heading will
be printed every time the specified number of records have been printed.
Careful specification of the text areas is necessary to achieve correct
results from this, and some experimentation is invariably necessary.
.sp
A number sign ("#") within the heading text will be replaced by the current
page number. The number sign will also be recognized within the detail
text, and permits the page number to be output for each record if desired.
.SH "Pause specification"
A question mark "?" at the end of the format file indicates that the
output should stop at this point before printing the next record. The
operator may then press ENTER to print the next record, or ^Z to stop
the printing.
.sp
This may be used for browsing through the file on the screen, or for
printing mail merge output onto single sheets of paper.
.SH "Uses of format files"
The format file facilities provide a range of formatting possibilities
beyond the immediately obvious. The following list provides some
suggestions, although these are by no means all the possibilities.
.sp
Alternative tabulations, with differing heading styles with page numbering
at specified points. Single, double, triple or indeed any line spacing.
Unit specifiers for value fields (e.g. 25143 cwt, 96 cc, etc.).
Fields duplicated on same or different lines.
.sp
Paged browsing, using nicely formatted screens containing the fields in
forms with fancy titles, side headings and borders.
.sp
Printout of database onto forms, again with headings etc.
.sp
Mail merge, using a letter produced on a word processor, with field
specifiers at appropriate points.
.sp
Formatting of data for input to a BASIC or other applications program.
(e.g. ">string field<",>numeric field<,constant data,>another field<).
.sp
Formatting of data to permit reformatting of the database file itself.
An example of this is given in the section on reformatting.
.sp
Label printing, using a simple format file.
.sp 2
And of course the data used in these various formats of output can come
from selected fields from a complex selection from more than one database.
.TH "Messages and Errors" MESSAGES "DBQ User manual"
.SH Introduction
The following list of messages describe the likely cause of each
error situation, and where applicable corrective action that may be
taken.
.SH "Informational messages"
These messages are printed for your information, and do not indicate that
anything has gone wrong.
.sp
The following informational messages will appear.
.sp
DBQ.LOG closed
.sp
.in 12
This is output when an EXIT command is issued, if the log file has been
opened using the SET LOG command. It is used to remind you that the log
file has been written, and that you should either do something with the
log file, or delete it to free up the disk space occupied.
.sp
.in 8
Fold = ?, verify = ?, log = ?, page = ??
.sp
.in 12
This is output whenever a SET command is issued, to show the resultant
settings.
.sp
.in 8
Press ENTER to continue, ^Z to quit, x for help on x...
.sp
.in 12
This is output after each page of HELP information displayed, and gives
you the option of viewing the next help page, quitting from the help
display, or looking for the next help topic starting with the given
letter.
.sp
.in 8
[ nnn records xxxxxx ]
.br
.in 10
Where xxxxxx can = Found, Updated, Inserted, Deleted or Freed.
.sp
.in 12
This is output whenever a PRINT, FIND, UPDATE, INSERT, DELETE or
COMPRESS command is issued, and indicates the number of records
taking part in the operation.
.sp
.in 8
Press ENTER to continue, ^Z to quit
.sp
.in 12
This is output after each record is printed, if the prompting
character is specified in a format file.
.sp
.in 8
nnnn swaps in nnnn passes
.sp
.in 12
This is output after a sort operation completes, to indicate how many
record swaps took place. A large number will indicate that the file was
not close to the sort order requested.
.br
.in 8
.bp
.SH "Error messages"
These messages indicate that the requested action could not be performed
as requested. In certain cases a complete query may have been recognized
up to the point of a syntax error. In this case, the query will have been
actioned, but error will also be reported.
.sp
These errors will be printed in the general form:-
.sp
### Error: error details ###
.sp
Where "error details" are specific error types, and will be one of the
following:-
.sp
syntax error
.sp
.in 12
This is the most common message and indicates that DBQ did not understand
the command that you issued. Generally, this means that you have typed
a command name or other keyword incorrectly, but can mean that the order
of the words in the command is incorrect, or that certain options are not
available on the command. It can also be caused by a string literal not being
enclosed in quotation marks.
.sp
If you are in doubt about what word the error was raised on, try typing the
command in one word to a line. As soon as an error is detected, the message
will be output. Whilst this helps in the majority of cases, in some cases
the actual word or construct in error is earlier on in the query, and is
only brought to light when the command is terminated.
.sp
.in 8
set parameter unknown
.sp
.in 12
An incorrect option has been specified for the SET command. Check the
spelling of options in the SET command.
.sp
.in 8
memory full
.sp
.in 12
This message indicates that the dynamic memory of the system is full.
.sp
If this message is reported, steps can be
taken to release some memory, by deleting unwanted procedures, and by
not using a log file. It is recommended that an exit is made, and
DBQ restarted, without defining unwanted procedures, and without
starting a log file. This will remove any fragmented dynamic memory,
and provide the maximum space to run a query. If this still fails,
the query will have to be reduced in size or complexity.
.sp
It may help on a query against a number of databases
to use the FIND command to select the required data into a temporary
database as an intermediate step, and reduce the overhead caused by
having a number of databases open. Quite often a little experimentation
will cure the problem.
.sp
.in 8
disk full
.sp
.in 12
This indicates that the disk to which you are writing does not have enough
space to write a file. You should exit from DBQ and tidy up the disk
before proceeding.
.sp
.in 8
database file not found
.sp
.in 12
The database file that you specified does not exist. Either you have
mispelled the name of the database, or you are using the wrong disk.
.sp
.in 8
database name undefined
.sp
.in 12
The database name specified in a selection expression has not been
used in the database name list of the command.
.sp
.in 8
bad file header
.sp
.in 12
The database file specified does not have a valid DBQ header section.
You may have renamed a non-DBQ file with the .DBQ extension, or the
database file may have been corrupted in some manner. Restore the latest
backup copy and bring the database up to date. If you don't have a backup
copy, read the section on database administration, ready for the next
disk corruption.
.sp
.in 8
creating database
.sp
.in 12
An error has occurred whilst creating the database. This may be due to
the database name already being in use, or the disk being write protected.
.sp
.in 8
field name duplicated
.sp
.in 12
When creating a database, a fieldname has been used more than once.
.sp
.in 8
field name ambiguous
.sp
.in 12
Within a query, a field name has been used that appears in more than one
of the databases named. In this case, the name should be qualified by
prefixing it with the database name and a full stop.
.sp
.in 8
expression too complex
.sp
.in 12
A selection expression has been used that is too complex to parse. It is
usually necessary to reduce the complexity. It may be sufficient to specify
the expression in a different manner.
.sp
.in 8
too many fields
.sp
.in 12
Too many fields have been specified for a database, during a CREATE
operation.
.sp
.in 8
reading record
.sp
.in 12
An error occurred when reading a record. This may be due to the disk
having been removed.
.sp
.in 8
writing record
.sp
.in 12
An error occurred when writing a record. This may be caused by the disk
being full, or by the disk being removed during the command.
.sp
.in 8
input file not found
.sp
.in 12
The input file specified cannot be found on the disk.
.sp
.in 8
creating output file
.sp
.in 12
An error occurred whilst attempting to create the specified output file.
This may be due to the file already existing, or the disk being write
protected.
.sp
.in 8
command file not found
.sp
.in 12
The specified command file could not be found on the disk.
.sp 2
.in 8
Also the following error messages may appear as shown:-
.sp
### Maximum line length is 128 ###
.br
Re-enter>
.sp
.in 12
An input line longer than the allowed maximum has been used. To
overcome this problem simply split the command up and enter it on
multiple lines.
.sp
.in 8
### Out of memory using <procname> ###
.sp
.in 12
Dynamic memory was exhausted when attempting to expand the named
procedure. See the 'memory full' error for an explanation of likely
causes and suggested remedial action.
.sp
.in 8
### Error opening ICF <filename> ###
.sp
.in 12
An error occurred when attempting to open the indirect command file named.
.sp
.in 8
### Procedure <procname> not found ###
.sp
.in 12
A definition for the named procedure could not be found.
.sp
.in 8
Sorry - help file not available.
.sp
.in 12
The help file 'DBQ.HLP' is not present on the disk. You will probably
have removed it due to its size. It is possible to produce a reduced
file with just the summary details on it by editing the full file, after
taking a backup copy. You can add any useful information or personal
notes and hints at the end of the file.
.br
.in 8
.bp
.SH "Other error situations"
Other occasions may arise where an erroneous situation can be assumed to
exist. Certain obscure combinations of circumstance may, perhaps, result
in the computer entering a "hard loop". This situation is easy to detect,
as it is impossible to break out of the program by the usual means of
pressing control-C, and the disk drives may remain running but not doing
anything. If this is the case (and you have not pressed a combination of
keys which switches the printer output on when a printer is not attached,
which results in an error message being displayed by CPM+ on the bottom
line of the screen after a delay of around 20 to 30 seconds),
it will be necessary to carefully shut the computer down and reboot CPM.
e screen after a delay of around 20 to 30 seconds),
it will be necessary to carefully